----------------------------------------------------------------------------------------
--
-- File name:   create_sql_profile.sql
--
-- Purpose:     Create SQL Profile based on Outline hints in V$SQL.OTHER_XML.
--
-- Author:      Kerry Osborne
--
-- Usage:       This scripts prompts for four values.
--
--              sql_id: the sql_id of the statement to attach the profile to (must be in the shared pool)
--
--              child_no: the child_no of the statement from v$sql
--
--              profile_name: the name of the profile to be generated
--
--              category: the name of the category for the profile
--
--              force_macthing: a toggle to turn on or off the force_matching feature
--
-- Description:
--
--              Based on a script by Randolf Giest.
--
-- Mods:        This is the 2nd version of this script which removes dependency on rg_sqlprof1.sql.
--
--              See kerryosborne.oracle-guy.com for additional information.
---------------------------------------------------------------------------------------
--
-- @rg_sqlprof1 '&&sql_id' &&child_no '&&category' '&force_matching'

SET feedback OFF
SET sqlblanklines ON
accept sql_id -
prompt 'Enter value for sql_id: ' -
DEFAULT 'X0X0X0X0'
accept child_no -
prompt 'Enter value for child_no (0): ' -
DEFAULT '0'
accept profile_name -
prompt 'Enter value for profile_name (PROF_sqlid_planhash): ' -
DEFAULT 'X0X0X0X0'
accept category -
prompt 'Enter value for category (DEFAULT): ' -
DEFAULT 'DEFAULT'
accept force_matching -
prompt 'Enter value for force_matching (FALSE): ' -
DEFAULT 'false'

DECLARE
  ar_profile_hints sys.sqlprof_attr;
  cl_sql_text CLOB;
  l_profile_name VARCHAR2(30);
BEGIN
  SELECT extractvalue(value(d), '/hint') AS outline_hints bulk collect
  INTO ar_profile_hints
  FROM xmltable('/*/outline_data/hint' passing
    (SELECT xmltype(other_xml) AS xmlval
    FROM v$sql_plan
    WHERE sql_id     = '&&sql_id'
    AND child_number =
      &&child_no
    AND other_xml IS NOT NULL
    ) ) d;
  SELECT sql_fulltext,
    DECODE('&&profile_name','X0X0X0X0','PROF_&&sql_id'
    ||'_'
    ||plan_hash_value,'&&profile_name')
  INTO cl_sql_text,
    l_profile_name
  FROM v$sql
  WHERE sql_id     = '&&sql_id'
  AND child_number =
    &&child_no;
  dbms_sqltune.import_sql_profile( sql_text => cl_sql_text, profile => ar_profile_hints, category => '&&category', name => l_profile_name, force_match => &&force_matching
  -- replace => true
  );
  dbms_output.put_line(' ');
  dbms_output.put_line('SQL Profile '||l_profile_name||' created.');
  dbms_output.put_line(' ');
EXCEPTION
WHEN NO_DATA_FOUND THEN
  dbms_output.put_line(' ');
  dbms_output.put_line('ERROR: sql_id: '||'&&sql_id'||' Child: '||'&&child_no'||' not found in v$sql.');
  dbms_output.put_line(' ');
END;
/

undef sql_id
undef child_no
undef profile_name
undef category
undef force_matching
SET sqlblanklines OFF
SET feedback ON
